package com.serviceprovider.business.module.sp.payee.services.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.serviceprovider.business.module.sp.payee.model.Payee;
import com.serviceprovider.business.module.sp.payee.model.PayeeProfile;
import com.serviceprovider.business.module.sp.payee.services.dao.interfaces.IPayeeProfileService;
import com.serviceprovider.business.module.sp.payee.valueobjects.PayeeSearchResultVO;
import com.serviceprovider.business.module.user.model.impl.UserSearchCriteria;
import org.springframework.stereotype.Service;

/**
 * This class is an implementation of PayeeProfileService
 * @author mrduongnv@gmail.com
 * 
 */
@Service("payeeProfileService")
public class PayeeProfileServiceDaoImpl extends JdbcDaoSupport implements IPayeeProfileService {

    @Autowired
    public PayeeProfileServiceDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }


	private static final String F_ID = "id";
	private static final String F_FIRST_NAME = "firstname";
	private static final String F_MIDDLE_NAME = "middlename";
	private static final String F_LAST_NAME = "lastname";
	private static final String F_EFIN = "efin";
	private static final String F_SSN = "ssn";
	private static final String F_BUSINESS_NAME = "businessname";
	private static final String F_FIRST_EMAIL = "firstemail";
	private static final String F_SECOND_EMAIL = "secondemail";
	private static final String F_PAGER = "pager";
	
	private static final String F_CONTACT_ADDRESS = "contact_address";
	private static final String F_CONTACT_STATE = "contact_state";
	private static final String F_CONTACT_ZIP = "contact_zip";
	private static final String F_CONTACT_FAX = "contact_fax";
	private static final String F_CONTACT_COUNTRY = "contact_country";
	private static final String F_CONTACT_COUNTY = "contact_county";
	private static final String F_CONTACT_CITY = "contact_city";
	private static final String F_CONTACT_PHONE = "contact_phone";
	private static final String F_CONTACT_PHONE_2 = "contact_phone_2";
	private static final String F_CONTACT_CELL = "contact_cell";
	private static final String F_CONTACT_CELL_PROVIDER = "contact_cell_provider";
	
	private static final String F_BUSINESS_ADDRESS = "business_address";
	private static final String F_BUSINESS_STATE = "business_state";
	private static final String F_BUSINESS_ZIP = "business_zip";
	private static final String F_BUSINESS_FAX = "business_fax";
	private static final String F_BUSINESS_COUNTRY = "business_country";
	private static final String F_BUSINESS_COUNTY = "business_county";
	private static final String F_BUSINESS_CITY = "business_city";
	private static final String F_BUSINESS_PHONE = "business_phone";	
	
	private static final String F_SHIPPING_ADDRESS = "shipping_address";
	private static final String F_SHIPPING_STATE = "shipping_state";
	private static final String F_SHIPPING_ZIP = "shipping_zip";
	private static final String F_SHIPPING_FAX = "shipping_fax";
	private static final String F_SHIPPING_COUNTRY = "shipping_country";
	private static final String F_SHIPPING_COUNTY = "shipping_county";
	private static final String F_SHIPPING_CITY = "shipping_city";
	private static final String F_SHIPPING_PHONE = "shipping_phone";
	
	private static final String F_NOTIFY_TYPE = "pref_message_notify_type";
	private static final String F_NOTIFY_ALERT = "pref_message_notify_alert";
	
	private static final String F_USER_ID = "user_id";
	
	private static final String INSERT_A_PROFILE
		= new StringBuilder("insert into adm_user_profiles (") 
			.append(F_FIRST_NAME).append(",")
			.append(F_MIDDLE_NAME).append(",")
			.append(F_LAST_NAME).append(",")
			.append(F_EFIN).append(",")
			.append(F_SSN).append(",")
			.append(F_BUSINESS_NAME).append(",")
			.append(F_FIRST_EMAIL).append(",")
			.append(F_SECOND_EMAIL).append(",")
			.append(F_PAGER).append(",")
			.append(F_CONTACT_ADDRESS).append(",")
			.append(F_CONTACT_STATE).append(",")
			.append(F_CONTACT_ZIP).append(",")
			.append(F_CONTACT_FAX).append(",")
			.append(F_CONTACT_COUNTRY).append(",")
			.append(F_CONTACT_COUNTY).append(",")
			.append(F_CONTACT_CITY).append(",")
			.append(F_CONTACT_PHONE).append(",")
			.append(F_CONTACT_PHONE_2).append(",")
			.append(F_CONTACT_CELL).append(",")
			.append(F_CONTACT_CELL_PROVIDER).append(",")
			.append(F_BUSINESS_ADDRESS).append(",")
			.append(F_BUSINESS_STATE).append(",")
			.append(F_BUSINESS_ZIP).append(",")
			.append(F_BUSINESS_FAX).append(",")
			.append(F_BUSINESS_COUNTRY).append(",")
			.append(F_BUSINESS_COUNTY).append(",")
			.append(F_BUSINESS_CITY).append(",")
			.append(F_BUSINESS_PHONE).append(",")
			.append(F_SHIPPING_ADDRESS).append(",")
			.append(F_SHIPPING_STATE).append(",")
			.append(F_SHIPPING_ZIP).append(",")
			.append(F_SHIPPING_FAX).append(",")
			.append(F_SHIPPING_COUNTY).append(",")
			.append(F_SHIPPING_COUNTRY).append(",")
			.append(F_SHIPPING_CITY).append(",")
			.append(F_SHIPPING_PHONE).append(",")
			.append(F_NOTIFY_TYPE).append(",")
			.append(F_USER_ID).append(",")
			.append(F_NOTIFY_ALERT).append(") values (?,?,?,?,?,?,?,?,?," +
					"?,?,?,?,?,?,?," +
					"?,?,?,?,?,?,?," +
					"?,?,?,?,?,?,?," +
					"?,?,?,?,?,?,?,?,?)") 
			.toString();

	private static final String UPDATE_A_PROFILE
		= new StringBuilder("update adm_user_profiles set ")
		.append(F_FIRST_NAME).append(" = ?, ")
		.append(F_MIDDLE_NAME).append(" = ?, ")
		.append(F_LAST_NAME).append(" = ?, ")
		.append(F_EFIN).append(" = ?, ")
		.append(F_SSN).append(" = ?, ")
		.append(F_BUSINESS_NAME).append(" = ?, ")
		.append(F_FIRST_EMAIL).append(" = ?, ")
		.append(F_SECOND_EMAIL).append(" = ?, ")
		.append(F_PAGER).append(" = ?, ")
		.append(F_CONTACT_ADDRESS).append(" = ?, ")
		.append(F_CONTACT_STATE).append(" = ?, ")
		.append(F_CONTACT_ZIP).append(" = ?, ")
		.append(F_CONTACT_FAX).append(" = ?, ")
		.append(F_CONTACT_COUNTRY).append(" = ?, ")
		.append(F_CONTACT_COUNTY).append(" = ?, ")
		.append(F_CONTACT_CITY).append(" = ?, ")
		.append(F_CONTACT_PHONE).append(" = ?, ")
		.append(F_CONTACT_PHONE_2).append(" = ?, ")
		.append(F_CONTACT_CELL).append(" = ?, ")
		.append(F_CONTACT_CELL_PROVIDER).append(" = ?, ")
		.append(F_BUSINESS_ADDRESS).append(" = ?, ")
		.append(F_BUSINESS_STATE).append(" = ?, ")
		.append(F_BUSINESS_ZIP).append(" = ?, ")
		.append(F_BUSINESS_FAX).append(" = ?, ")
		.append(F_BUSINESS_COUNTRY).append(" = ?, ")
		.append(F_BUSINESS_COUNTY).append(" = ?, ")
		.append(F_BUSINESS_CITY).append(" = ?, ")
		.append(F_BUSINESS_PHONE).append(" = ?, ")
		.append(F_SHIPPING_ADDRESS).append(" = ?, ")
		.append(F_SHIPPING_STATE).append(" = ?,")
		.append(F_SHIPPING_ZIP).append(" = ?, ")
		.append(F_SHIPPING_FAX).append(" = ?, ")
		.append(F_SHIPPING_COUNTRY).append(" = ?, ")
		.append(F_SHIPPING_COUNTY).append(" = ?, ")
		.append(F_SHIPPING_CITY).append(" = ?, ")
		.append(F_SHIPPING_PHONE).append(" = ?, ")
		.append(F_NOTIFY_TYPE).append(" = ?, ")		
		.append(F_USER_ID).append(" = ?, ")
		.append(F_NOTIFY_ALERT).append(" = ? ")
		.append("where ").append(F_ID).append(" = ?")
		.toString();
	
	private static final String DELETE_PROFILE_BY_USERID
		= new StringBuilder("delete from adm_user_profiles where ")
		.append(F_USER_ID).append(" = ?")
		.toString();

	private static final String DELETE_A_PROFILE
		= new StringBuilder("delete from adm_user_profiles where ")
		.append(F_ID).append(" = ?")
		.toString();

	private static final String SELECT_A_PROFILE
		= new StringBuilder("select * from adm_user_profiles where ")
		.append(F_ID).append(" = ?")
		.toString();
	
	private static final String SELECT_PROFILE_BY_USERID
		= new StringBuilder("select * from adm_user_profiles where ")
		.append(F_USER_ID).append(" = ?")
		.toString();
	
	private static final String SEARCH_PROFILE
		= new StringBuffer("select pro.*, usr.id as userid, usr.email as useremail from adm_user_profiles pro, "
						 + " adm_users usr,role rol where pro.")
		.append(F_USER_ID).append(" = usr.id")
		.append(" and usr.role=rol.id and rol.id = ")
		.toString();
	
	private static final String GET_TRANSACTIONS_BY_STATUS
		= new StringBuffer("select count(transaction_id) from sp_transaction_details tr")
		.append(", adm_users usr where tr.user_id=usr.id ")
		.append("and usr.id=? ")
		.append("and tr.transaction_status=?")
		.toString();
	
	private static final String GET_ALL_TRANSACTIONS
	= new StringBuffer("select count(transaction_id) from sp_transaction_details tr")
	.append(", adm_users usr where tr.user_id=usr.id ")
	.append("and usr.id=? ")
	.toString();
	
	private static final String GET_ORDERS_BY_STATUS
		= new StringBuffer("select count(order_id) from sp_order_details ord, adm_users usr")
		.append(" where ord.user_id=usr.id and usr.id=?")
		.append(" and ord.order_status_id=?")
		.toString();
	
	private static final String GET_ORDER_STATUS
		= new StringBuffer("select id from order_status")
		.toString();
	

//    @Autowired
//    private ITransStatusDao transactionStatusDao;

	
	public void save(PayeeProfile profile, int payeeId) {
		String sql = profile.getId() == null
						? INSERT_A_PROFILE : UPDATE_A_PROFILE;
		getJdbcTemplate().update(sql, getParams(profile, payeeId));
	}
	
	private Object[] getParams(PayeeProfile profile, int payeeId) {
		Object[] insertParams = fillParams(profile,payeeId);
		if (profile.getId() == null) {
			return insertParams;
		} else {
			Object[] updateParams = new Object[insertParams.length + 1];
            System.arraycopy(insertParams, 0, updateParams, 0, updateParams.length - 1);
			updateParams[updateParams.length - 1] = profile.getId();
			
			return updateParams;
		}
	}
	
	private Object[] fillParams(PayeeProfile profile, int payeeId) {
		return new Object[] {
			profile.getFirstName(),
			profile.getMiddleName(),
			profile.getLastName(),
			profile.getEfin(),
			profile.getSsn(),
			profile.getBusinessName(),
			profile.getFirstEmail(),
			profile.getSecondEmail(),
			profile.getPager(),
			profile.getContactAddress(),
			profile.getContactState(),
			profile.getContactZip(),
			profile.getContactFax(),
			profile.getContactCountry(),
			profile.getContactCounty(),
			profile.getContactCity(),			
			profile.getFirstContactPhone(),
			profile.getSecondContactPhone(),
			profile.getContactCell(),
			profile.getContactCellProvider(),
			profile.getBusinessAddress(),
			profile.getBusinessState(),
			profile.getBusinessZip(),
			profile.getBusinessFax(),
			profile.getBusinessCountry(),
			profile.getBusinessCounty(),
			profile.getBusinessCity(),			
			profile.getBusinessPhone(),
			profile.getShippingAddress(),
			profile.getShippingState(),
			profile.getShippingZip(),
			profile.getShippingFax(),
			profile.getShippingCountry(),
			profile.getShippingCounty(),
			profile.getShippingCity(),			
			profile.getShippingPhone(),
			profile.getPrefMessageNotifyType(),
			payeeId,
			profile.getPrefMessageNotifyAlert()			
		};
	}
	
	public void removeByPayeeId(int payeeId) {
		getJdbcTemplate().update(DELETE_PROFILE_BY_USERID,
									new Object[] { payeeId });
	}

	public void remove(int id) {
		getJdbcTemplate().update(DELETE_A_PROFILE, new Object[] { id });		
	}
	
	private PayeeProfile getProfile(String query, Object[] params) {
		RowMapper mapper = new RowMapper() {
			public PayeeProfile mapRow(ResultSet rs, int rowNum) throws SQLException {
				PayeeProfile profile = new PayeeProfile();
				
				profile.setId(rs.getInt(F_ID));
				profile.setFirstName(rs.getString(F_FIRST_NAME));
				profile.setMiddleName(rs.getString(F_MIDDLE_NAME));
				profile.setLastName(rs.getString(F_LAST_NAME));
				profile.setEfin(rs.getString(F_EFIN));
				profile.setSsn(rs.getString(F_SSN));
				profile.setBusinessName(rs.getString(F_BUSINESS_NAME));
				profile.setFirstEmail(rs.getString(F_FIRST_EMAIL));
				profile.setSecondEmail(rs.getString(F_SECOND_EMAIL));
				profile.setPager(rs.getString(F_PAGER));
				
				profile.setContactAddress(rs.getString(F_CONTACT_ADDRESS));
				profile.setContactState(rs.getString(F_CONTACT_STATE));
				profile.setContactZip(rs.getString(F_CONTACT_ZIP));
				profile.setContactFax(rs.getString(F_CONTACT_FAX));
				profile.setContactCountry(rs.getString(F_CONTACT_COUNTRY));
				profile.setContactCity(rs.getString(F_CONTACT_COUNTY));
				profile.setContactCounty(rs.getString(F_CONTACT_CITY));
				profile.setFirstContactPhone(rs.getString(F_CONTACT_PHONE));
				profile.setSecondContactPhone(rs.getString(F_CONTACT_PHONE_2));
				profile.setContactCell(rs.getString(F_CONTACT_CELL));
				profile.setContactCellProvider(rs.getString(F_CONTACT_CELL_PROVIDER));
				
				profile.setBusinessAddress(rs.getString(F_BUSINESS_ADDRESS));
				profile.setBusinessState(rs.getString(F_BUSINESS_STATE));
				profile.setBusinessZip(rs.getString(F_BUSINESS_ZIP));
				profile.setBusinessFax(rs.getString(F_BUSINESS_FAX));
				profile.setBusinessCountry(rs.getString(F_BUSINESS_COUNTRY));
				profile.setBusinessCity(rs.getString(F_BUSINESS_CITY));
				profile.setBusinessCounty(rs.getString(F_BUSINESS_COUNTY));
				profile.setBusinessPhone(rs.getString(F_BUSINESS_PHONE));
				
				profile.setShippingAddress(rs.getString(F_SHIPPING_ADDRESS));
				profile.setShippingState(rs.getString(F_SHIPPING_STATE));
				profile.setShippingZip(rs.getString(F_SHIPPING_ZIP));
				profile.setShippingFax(rs.getString(F_SHIPPING_FAX));
				profile.setShippingCountry(rs.getString(F_SHIPPING_COUNTRY));
				profile.setShippingCity(rs.getString(F_SHIPPING_COUNTY));
				profile.setShippingCounty(rs.getString(F_SHIPPING_CITY));
				profile.setShippingPhone(rs.getString(F_SHIPPING_PHONE));
				profile.setPrefMessageNotifyType(rs.getInt(F_NOTIFY_TYPE));
				profile.setPrefMessageNotifyAlert(rs.getInt(F_NOTIFY_ALERT));
				profile.setPayeeId(rs.getInt(F_USER_ID));
				
				return profile;
			}
		};

		try {
			return (PayeeProfile) getJdbcTemplate().queryForObject(query, params, mapper);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}				
	}
	
	public PayeeProfile getById(int id) {
		
		return getProfile(SELECT_A_PROFILE, new Object[] { id });
	}
	
	public PayeeProfile getByPayee(int payeeId) {
		return getProfile(SELECT_PROFILE_BY_USERID, new Object[] { payeeId });
	}
	
	public List<PayeeSearchResultVO> getBySearchCriteria(UserSearchCriteria criteria) {
//		String query = SEARCH_PROFILE;
//		query += criteria.getRoleId();
//		
//		if (criteria.getBusinessName() != null
//				&& !criteria.getBusinessName().equals("")) {
//			query += " and pro." + F_BUSINESS_NAME; 
//			query += " like '%" + criteria.getBusinessName() + "%'";
//		}
//		
//		if (criteria.getCountry() != null
//				&& !criteria.getCountry().equals("")) {
//			query += " and pro." + F_CONTACT_COUNTRY; 
//			query += " like '%" + criteria.getCountry() + "%'";
//		}
//		
//		if (criteria.getCity() != null
//				&& !criteria.getCity().equals("")) {
//			query += " and pro." + F_CONTACT_CITY; 
//			query += " like '%" + criteria.getCity() + "%'";
//		}
//		
//		if (criteria.getZip() != null
//				&& !criteria.getZip().equals("")) {
//			query += " and pro." + F_CONTACT_ZIP; 
//			query += " like '%" + criteria.getZip() + "%'";
//		}		
//
//		if (criteria.getEfin() != null
//				&& !criteria.getEfin().equals("")) {
//			query += " and pro." + F_EFIN; 
//			query += " like '%" + criteria.getEfin() + "%'";
//		}
//
//		if (criteria.getEmail() != null
//				&& !criteria.getEmail().equals("")) {
//			query += " and usr.email"; 
//			query += " like '%" + criteria.getEmail() + "%'";
//		}
//		
//		System.out.println("Role Id = " + criteria.getRoleId());
//		System.out.println("SQL = " + query+";");
//		
//		List list = getJdbcTemplate().queryForList(query);
//		
//		List<PayeeSearchResultVO> listResult
//				= new ArrayList<PayeeSearchResultVO>();
//		
//		Map row = null;
//		PayeeProfile profile = null;
//		PayeeSearchResultVO vo = null;
//
//		Payee payee = null;
//		
//		// Gets order statuses
//		List listOrdSt = getJdbcTemplate().queryForList(GET_ORDER_STATUS);
//		List<Integer> listOrderStatuses = new ArrayList<Integer>();
//		
//		for (Object ord : listOrdSt) {
//			Map m = (Map) ord;
//			int statusId = ((Long) m.get("id")).intValue();
//			listOrderStatuses.add(statusId);
//		}
//		
//		for (Object obj : list) {
//			profile = new PayeeProfile();
//			vo = new PayeeSearchResultVO();
//			payee = new Payee();
//			
//			row = (Map) obj;
//			profile.setId(((Long) row.get(F_ID)).intValue());
//			profile.setPayeeId(((Long) row.get("userid")).intValue());
//			profile.setFirstName((String) row.get(F_FIRST_NAME));
//			profile.setMiddleName((String) row.get(F_MIDDLE_NAME));
//			profile.setLastName((String) row.get(F_LAST_NAME));
//			profile.setBusinessName((String) row.get(F_BUSINESS_NAME));
//			profile.setEfin((String) row.get(F_EFIN));
//			
//			payee.setId(((Long) row.get("userid")).intValue());
//			payee.setEmail((String) row.get("useremail"));
//			
//			vo.setProfile(profile);
//			vo.setPayee(payee);
//			
//			int newTrans = 0;
//			int step1Trans = 0;
//			int step2Trans = 0;
//			int unassignedTrans = 0;
//			int totalTrans = 0;
//			try{
//			// Query transaction information
//			Object[] params = new Object[] {payee.getId(), transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_NEW_ID).getName()};
//			newTrans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
//			
//			params = new Object[] {payee.getId(), transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_APPROVED_ID).getName()};
//			step1Trans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
//
//			params = new Object[] {payee.getId(), transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_ORDERED_ID).getName()};
//			step2Trans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
//			
//			params = new Object[] {payee.getId(), transactionStatusDao.get(AppSettings.TRANSACTION_STATUS_LOST_ID).getName()};
//			unassignedTrans = getJdbcTemplate().queryForInt(GET_TRANSACTIONS_BY_STATUS, params);
//			
//			// total transactions
//			totalTrans = getJdbcTemplate().queryForInt(GET_ALL_TRANSACTIONS, new Object[] {payee.getId()});
//			
//			}catch(Exception e){
//				
//			}
//			vo.setNewTransactionCount(newTrans);
//			vo.setStep1TransactionCount(step1Trans);
//			vo.setStep2TransactionCount(step2Trans);
//			vo.setUnassignedTransactionCount(unassignedTrans);
//			
//			vo.setOtherTransactionCount(totalTrans - (newTrans + step1Trans + step2Trans + unassignedTrans));
//			
//			// order statistics
//			List<Integer> lstOrderCounts = new ArrayList<Integer>();
//			
//			for (int ordStatusId : listOrderStatuses) {
//				int ordCount = getJdbcTemplate().queryForInt(GET_ORDERS_BY_STATUS, new Object[] {payee.getId(), ordStatusId});
//				lstOrderCounts.add(ordCount);
//			}
//			
//			vo.setOrderCounts(lstOrderCounts);
//			
//			listResult.add(vo);
//		}		
//		
//		return listResult;
		return null;
	}

	public List<PayeeSearchResultVO> getByRole(int roleId) {
		String query = SEARCH_PROFILE;
		
		if (roleId == 0) {
			// if roleId = 0 then return all user profiles
			query = query.replace(" and rol.id = ", "");
		} else {
			// filter user profiles by role
			query += roleId;
		}
		
		System.out.println("Role Id = " + roleId);
		System.out.println("SQL = " + query);
		
		List list = getJdbcTemplate().queryForList(query);
		
		List<PayeeSearchResultVO> listResult
				= new ArrayList<PayeeSearchResultVO>();
		
		Map row = null;
		PayeeProfile profile = null;
		PayeeSearchResultVO vo = null;
		Payee payee = null;
		
		for (Object obj : list) {
			profile = new PayeeProfile();
			vo = new PayeeSearchResultVO();
			payee = new Payee();
			
			row = (Map) obj;
			profile.setId(((Long) row.get(F_ID)).intValue());
			profile.setPayeeId(((Long) row.get("userid")).intValue());
			profile.setFirstName((String) row.get(F_FIRST_NAME));
			profile.setMiddleName((String) row.get(F_MIDDLE_NAME));
			profile.setLastName((String) row.get(F_LAST_NAME));
			profile.setBusinessName((String) row.get(F_BUSINESS_NAME));
			profile.setEfin((String) row.get(F_EFIN));
			
			payee.setId(((Long) row.get("userid")).intValue());
			payee.setEmail((String) row.get("useremail"));
			
			vo.setProfile(profile);
			vo.setPayee(payee);
			
			listResult.add(vo);
		}		
		
		return listResult;		
	}
}
